SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE procedure [utils].[split_into_rowscols]
@in_inputtext nvarchar(max)  
,@in_rowdelimiter nvarchar(2)='^'  
,@in_coldelimiter nvarchar(2)=','  
as  
begin  
declare   
@lv_teststring nvarchar(max),  
@rowscount int,  
@colscount int,  
@dynsql nvarchar(max),  
@rowdelimiter nvarchar(2)  
,@coldelimiter nvarchar(2)  
  
--set @lv_teststring='l1,a1,c1^l2,a2,c2^'  
set @lv_teststring=@in_inputtext  
set @rowdelimiter=@in_rowdelimiter--'^'  
set @coldelimiter=@in_coldelimiter--','  
select *  
into #temprows  
from  
(select * from  [utils].[udf_SplitString] (@lv_teststring,@rowdelimiter))litems  
  
select @rowscount=@@rowcount  
select #temprows.id rowid,litems.*  
into #tempcols  
from  
#temprows  
cross apply   
(select * from  [utils].[udf_SplitString](elementvalue,@coldelimiter))litems  
 
select @colscount=max(cols.id) from #tempcols cols  
group by rowid  
  
declare @rowctr int, @colctr int, @staticprefixsql nvarchar(max),  @staticsuffixsql nvarchar(max),@finalsql nvarchar(max)  
set @dynsql=''  
set  @colctr=1  
  
set @staticprefixsql='select rows.numberid as rowid,'  
set @staticsuffixsql='from  
(  
  
select rows.id as rowid1,cols.* , max(rows.id) over () as reccnt,max(cols.id) over (partition by rows.id) as colCount  
from  
#tempcols cols  
join  
#temprows rows  
on  
rows.id=cols.rowid  
) RS  
join  
(  
select * from utils.numbers   
)rows  
on rows.numberid between 1 and reccnt and rowid1=rows.numberid  
--where rs.reccnt <=rows.numberid  
join  
(  
select * from utils.numbers   
)cols  
on cols.numberid between 1 and colCount and rs.id=cols.numberid  
group by rows.numberid'  
  
while @colctr <= @colscount  
begin  
select @dynsql= @dynsql + 'max(case when cols.numberid=' + cast(@colctr as nvarchar(10))+' then elementvalue end) as col' + cast(@colctr as nvarchar(10))  
if @colctr  <>  @colscount set @dynsql= @dynsql +','  
else set @dynsql= @dynsql +' '  
  
  
set @colctr= @colctr+1  
end  
  
set @finalsql = @staticprefixsql + @dynsql + @staticsuffixsql  
--select * from #tempcols  
  
print @finalsql  
exec (@finalsql)  
-------- here, processing ends fr staging /temp data.  
  
end  
GO
